package com.helloapp.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.helloapp.dao.StudentInfoDao;
import com.helloapp.entity.StudentInfo;
import com.helloapp.util.JdbcUtil;

public class StudentInfoDaoImpl implements StudentInfoDao {

	@Override
	public void add(StudentInfo studentInfo) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(StudentInfo studentInfo) {
		// TODO Auto-generated method stub

	}

	@Override
	public void update(StudentInfo studentInfo) {
		// TODO Auto-generated method stub

	}

	@Override
	public List<StudentInfo> queryAll() {
		// 1. 加载驱动
		try {
			// 2. 建立连接
			Connection con = JdbcUtil.getConnection();
			String sql = "select  `id`, `name`, `sex`, `age`, `weight`, `height` from `students_info`";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			List<StudentInfo> lists = new ArrayList<>();
			while (res.next()) {
				int id = res.getInt("id");
				String name = res.getString("name");
				String sex = res.getString("sex");
				int age = res.getInt("age");
				float weight = res.getFloat("weight");
				float height = res.getFloat("height");
				lists.add(new StudentInfo(id, name, sex, age, weight, height));
			}
			// 6. 释放资源
			JdbcUtil.free(res, stm, con);

			return lists;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public StudentInfo getById(Serializable id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<StudentInfo> queryPage(int pos, int page) {
		// 1. 加载驱动
		try {
			// 2. 建立连接
			Connection con = JdbcUtil.getConnection();
			String sql = "select  `id`, `name`, `sex`, `age`, `weight`, `height` from `students_info` limit ?,?";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			stm.setInt(1, pos);
			stm.setInt(2, page);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			List<StudentInfo> lists = new ArrayList<>();
			while (res.next()) {
				int id = res.getInt("id");
				String name = res.getString("name");
				String sex = res.getString("sex");
				int age = res.getInt("age");
				float weight = res.getFloat("weight");
				float height = res.getFloat("height");
				lists.add(new StudentInfo(id, name, sex, age, weight, height));
			}
			// 6. 释放资源
			JdbcUtil.free(res, stm, con);

			return lists;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public int count() {
		int result = 0;
		// 1. 加载驱动
		try {
			// 2. 建立连接
			Connection con = JdbcUtil.getConnection();
			String sql = "select count(*) from students_info";
			// 3. 创建Statement
			PreparedStatement stm = con.prepareStatement(sql);
			// 4. 执行SQL语句
			ResultSet res = stm.executeQuery();
			// 5. 处理结果
			while (res.next()) {
				result = res.getInt(1);
			}
			// 6. 释放资源
			JdbcUtil.free(res, stm, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(result);
		return result;
	}

}
